﻿using System.Data.SQLite;
using DVDTek_Data_Object.Collection;
using DVDTek_Data_Object.DataObject;
using System.Collections;

namespace DVDTek_Data_Object.Accessor
{
    class VideoAccessor
    {
        #region Constants
        internal const string TABLE_NAME = "Video";
        internal const string FN_PRIMARY_KEY = "id";
        private const string FN_ID = "id";
        private const string FN_SYNOPSIS = "synopsis";
        private const string FN_PERIOD = "period";
        private const string FN_MAIN_LANGUAGE = "main_language";
        private const string FN_OTHER_LANGUAGE = "other_language";
        private const string FN_SUBTITLE = "subtitle";
        private const string FN_VIDEO_FORMAT = "video_format";
        private const string FN_AUDIO_FORMAT = "audio_format";
        private const string FN_RESOLUTION = "resolution";
        private const string FN_FREQUENCY = "frequency";
        private const string FN_SIZE = "size";
        #endregion

        #region Members
        private static VideoAccessor videoAccessor;
        internal static VideoAccessor Instance
        {
            get
            {
                if (videoAccessor == null)
                    videoAccessor = new VideoAccessor();

                return videoAccessor;
            }
        }

        internal string CreateTableQuery
        {
            get
            {
                return string.Concat(
                    "CREATE TABLE ", TABLE_NAME, " (",
                    FN_ID, " integer PRIMARY KEY AUTOINCREMENT NOT NULL, ",
                    FN_SYNOPSIS, " varchar(20000) NOT NULL, ",
                    FN_PERIOD, " integer(4) NOT NULL, ",
                    FN_MAIN_LANGUAGE, " varchar(30) NOT NULL, ",
                    FN_OTHER_LANGUAGE, " varchar(30) NULL, ",
                    FN_SUBTITLE, " varchar(30) NULL, ",
                    FN_VIDEO_FORMAT, " varchar(10) NULL, ",
                    FN_AUDIO_FORMAT, " varchar(10) NULL, ",
                    FN_RESOLUTION, " varchar(12) NULL, ",
                    FN_FREQUENCY, " integer(3) NULL, ",
                    FN_SIZE, " integer NULL);");
            }
        }
        #endregion

        #region Constructors
        private VideoAccessor()
        {
        }
        #endregion

        #region Insert / Update / Delete
        internal int Insert(string synopsis, int period, string main_language, string other_language,
            string subtitle, string video_format, string audio_format, string resolution, int frequency, int size)
        {
            string query = string.Concat(
                "INSERT INTO ", TABLE_NAME,
                " (", FN_ID, ",", FN_SYNOPSIS,
                ",", FN_PERIOD, ",", FN_MAIN_LANGUAGE,
                ",", FN_OTHER_LANGUAGE, ",", FN_SUBTITLE,
                ",", FN_VIDEO_FORMAT, ",", FN_AUDIO_FORMAT,
                ",", FN_RESOLUTION, ",", FN_FREQUENCY, ",", FN_SIZE, ")",
                " VALUES ",
                "('', '", Common.QuoteEscape(synopsis), "',",
                "'", period, "','", Common.QuoteEscape(main_language), "',",
                "'", Common.QuoteEscape(other_language), "','", Common.QuoteEscape(subtitle), "',",
                "'", Common.QuoteEscape(video_format), "','", Common.QuoteEscape(audio_format), "',",
                "'", Common.QuoteEscape(resolution), "','", frequency, "','", size, "');",
                "SELECT last_insert_rowid();");

            return (int)Common.ExecuteScalarQuery(query);
        }

        internal void Update(int id, string synopsis, int period, string main_language, string other_language,
            string subtitle, string video_format, string audio_format, string resolution, int frequency, int size)
        {
            string query = string.Concat(
                "UPDATE ", TABLE_NAME, " SET ",
                FN_SYNOPSIS, "='", Common.QuoteEscape(synopsis), "',",
                FN_PERIOD, "='", period, "',",
                FN_MAIN_LANGUAGE, "='", Common.QuoteEscape(main_language), "',",
                FN_OTHER_LANGUAGE, "='", Common.QuoteEscape(other_language), "',",
                FN_SUBTITLE, "='", Common.QuoteEscape(subtitle), "',",
                FN_VIDEO_FORMAT, "='", Common.QuoteEscape(video_format), "',",
                FN_AUDIO_FORMAT, "='", Common.QuoteEscape(audio_format), "',",
                FN_RESOLUTION, "='", Common.QuoteEscape(resolution), "',",
                FN_FREQUENCY, "='", frequency, "',",
                FN_SIZE, "='", size, "' ",
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }

        internal void Delete(int id)
        {
            string query = string.Concat(
                "DELETE FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }
        #endregion

        #region Select
        internal VideoDataObject Select(int id)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
                return new VideoDataObject(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2),
                    reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6),
                    reader.GetString(7), reader.GetString(8), reader.GetInt32(9), reader.GetInt32(10));
            else
                return null;
        }

        internal VideoCollection SelectAll()
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            VideoCollection videos = new VideoCollection();

            while (reader.Read())
                videos.Add(new VideoDataObject(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2),
                    reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6),
                    reader.GetString(7), reader.GetString(8), reader.GetInt32(9), reader.GetInt32(10)));

            return videos;
        }
        #endregion

        #region Select Distinct
        internal ArrayList SelectAllPeriod()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_PERIOD, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList periods = new ArrayList();

            while (reader.Read())
                periods.Add(reader.GetInt32(0));

            return periods;
        }

        internal ArrayList SelectAllMainLanguage()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_MAIN_LANGUAGE, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList mainLanguages = new ArrayList();

            while (reader.Read())
                mainLanguages.Add(reader.GetString(0));

            return mainLanguages;
        }

        internal ArrayList SelectAllOtherLanguage()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_OTHER_LANGUAGE, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList otherLanguages = new ArrayList();

            while (reader.Read())
                otherLanguages.Add(reader.GetString(0));

            return otherLanguages;
        }

        internal ArrayList SelectAllSubtitle()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_SUBTITLE, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList subtitles = new ArrayList();

            while (reader.Read())
                subtitles.Add(reader.GetString(0));

            return subtitles;
        }

        internal ArrayList SelectAllVideoFormat()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_VIDEO_FORMAT, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList videoFormats = new ArrayList();

            while (reader.Read())
                videoFormats.Add(reader.GetString(0));

            return videoFormats;
        }

        internal ArrayList SelectAllAudioFormat()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_AUDIO_FORMAT, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList audioFormats = new ArrayList();

            while (reader.Read())
                audioFormats.Add(reader.GetString(0));

            return audioFormats;
        }

        internal ArrayList SelectAllResolution()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_RESOLUTION, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList resolutions = new ArrayList();

            while (reader.Read())
                resolutions.Add(reader.GetString(0));

            return resolutions;
        }

        internal ArrayList SelectAllFrequency()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_FREQUENCY, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList frequencies = new ArrayList();

            while (reader.Read())
                frequencies.Add(reader.GetInt32(0));

            return frequencies;
        }

        internal ArrayList SelectAllSize()
        {
            string query = string.Concat(
                "SELECT DISTINCT ", FN_SIZE, " FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            ArrayList sizes = new ArrayList();

            while (reader.Read())
                sizes.Add(reader.GetInt32(0));

            return sizes;
        }
        #endregion
    }
}
